package com.zhiyou.dao;

import com.zhiyou.model.*;
import com.zhiyou.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class UserListDao {
    public int getUserListNum(HashMap<String, String> keywordMap) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        int total=0;
        String keyword = keywordMap.get("keyword");
        String sql="select count(uid) total from user where uname like concat(\"%\",?,\"%\")";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,keyword==null?"":keyword);
            System.out.println("日志 : 查个数sql"+ps.toString());
            rs=ps.executeQuery();
            while (rs.next()){
                total = rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return total;

    }

    public List<User> getUser(int pageNo, int pageSize,HashMap<String, String> keywordMap) {
        ArrayList<User> users = new ArrayList<>();
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        String keyword = keywordMap.get("keyword");
        String sql="select * from user where uname like concat(\"%\",?,\"%\") limit ?,?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,keyword == null? "":keyword);
            ps.setInt(2,(pageNo-1)*pageSize);
            ps.setInt(3,pageSize);
            System.out.println("日志 : 查全部sql"+ps.toString());
            rs = ps.executeQuery();
            while(rs.next()){
                User user = new User();
                user.setUid(rs.getInt("uid"));
                user.setUname(rs.getString("uname"));
                user.setUpassword(rs.getString("upassword"));
                user.setUrealname(rs.getString("urealname"));
                user.setUroid(rs.getInt("uroid"));
                user.setUaddTime(rs.getDate("uaddTime"));
                user.setUstatus(rs.getInt("ustatus"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return users;
    }

    public List<User> getUserRole(List<User> users) {
        for (User user:users) {
            Connection conn = DBUtil.getConn();
            PreparedStatement ps=null;
            ResultSet rs=null;
            ArrayList<Role> roles = new ArrayList<>();
            String sql="select r.roid,roname,rodesc,roaddTime from role r,user_role ur where r.roid=ur.roid and ur.uid=?";
            try {
                ps=conn.prepareStatement(sql);
                ps.setInt(1,user.getUid());
                System.out.println("日志 : 查全部sql"+ps.toString());
                rs = ps.executeQuery();
                while(rs.next()){
                    Role role = new Role();
                    role.setRoid(rs.getInt("roid"));
                    role.setRoname(rs.getString("roname"));
                    role.setRodesc(rs.getString("rodesc"));
                    role.setRoaddTime(rs.getDate("roaddTime"));
                    roles.add(role);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                DBUtil.closeAll(conn,ps,rs);
            }
            user.setRoles(roles);
        }

        return users;
    }

    public void deleteById(int uid) {
        Connection conn = DBUtil.getConn();
        String sql="delete from user where uid=?";
        PreparedStatement ps=null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }

    public void addNew(User user) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;

        String sql="insert into user(uname,upassword,urealname,uroid,uaddTime,ustatus) values(?,?,?,?,?,?)";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,user.getUname());
            ps.setString(2,user.getUpassword());
            ps.setString(3,user.getUrealname());
            ps.setInt(4,user.getUroid());
            ps.setDate(5,new java.sql.Date(user.getUaddTime().getTime()));
            ps.setInt(6,user.getUstatus());
            System.out.println("日志 : sql="+ps.toString());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }

    public User selectById(int uid) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        User user=null;
        String sql="select * from user where uid=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,uid);
            System.out.println("日志 : 查个数sql"+ps.toString());
            rs=ps.executeQuery();
            while (rs.next()){
                user = new User();
                user.setUid(rs.getInt("uid"));
                user.setUname(rs.getString("uname"));
                user.setUpassword(rs.getString("upassword"));
                user.setUrealname(rs.getString("urealname"));
                user.setUroid(rs.getInt("uroid"));
                user.setUaddTime(rs.getDate("uaddTime"));
                user.setUstatus(rs.getInt("ustatus"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return user;
    }

    public void updateNew(User user) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        String sql="update user set uname=?,upassword=?,urealname=?,uroid=?,uaddTime=?,ustatus=? where uid=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,user.getUname());
            ps.setString(2,user.getUpassword());
            ps.setString(3,user.getUrealname());
            ps.setInt(4,user.getUroid());
            ps.setDate(5,new java.sql.Date(user.getUaddTime().getTime()));
            ps.setInt(6,user.getUstatus());
            ps.setInt(7,user.getUid());
            System.out.println("日志 : sql="+ps.toString());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }
}
